C:/Projekte/RAIS/Specific Masks/Import.aspx.cs

Go to the documentation of this file.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using RAIS.UI.Common;
using RAIS.Common.UserManagement;
using IcSRS_Import;
using System.Collections.Generic;
using System.Drawing;
using System.Data.SqlClient;
//using RAIS.ApplicationLogicLayer;

namespace RAIS.UI.SpecificMasks
{
    public partial class Import : System.Web.UI.Page
    {
        private IcSRS_Import.IcSRS_Import import;

                #region Properties

                protected UserData CurrentUser
                {
                        get { return (UserData)this.Session[UI.Common.Constants.CURRENT_USER_TAG]; }
                }

                #endregion
                #region Event handlers

        protected void Page_Load(object sender, EventArgs e)
        {
                        this.Response.Cache.SetNoStore();
                        if (this.CurrentUser == null)
                                UI.Common.AccessManagement.SignOut(this);

            if (!this.IsPostBack)
            {
                this.LBLusernameNoTranslation.Text = this.CurrentUser.Name;
                UI.Common.PageManagement.InitializePage(this, this.TreeView1);
                import = new IcSRS_Import.IcSRS_Import();

                // Create selected item counter which is used for the import-button text and the LBLsearch label
                Session["SelectedItemCount"] = 0;

                /* Store the Search Result as a DataTable in a session attribute.
                 * This is mandatory for the PageIndexChanging Event!
                 */
                Session["SearchResult"] = import.getSearchResult(Request["search"], Request["type"].ToString()
                                        , this.Session[RAIS.UI.Common.Constants.IcSRS_LOGIN_TAG].ToString()
                                        , this.Session[RAIS.UI.Common.Constants.IcSRS_PASSWORD_TAG].ToString());

                // Set GridView datasource
                GVsearchResult.DataSource = (DataTable)Session["SearchResult"];
                GVsearchResult.DataBind();

                if (GVsearchResult.Rows.Count > 0) 
                    LBLsearch.Text = "Search result for: \"" + Request["search"] + "\" in " + Request["type"] + "s";
                else // There are no search results
                {
                    LBLsearch.Text = "Sorry there were no search results for \"" + Request["search"] + "\" in " + Request["type"] + "s";
                    BTNimport1.Visible = false;
                    BTNback.Visible = true;
                }
            }
            this.LBLinput.ForeColor = RAIS.UI.Common.Constants.COLOR_OF_MENU_BAR_TEXT_HIGHLIGHT;
//                      this.LBTNmessagebox.Visible = !(this.CurrentUser.FunctionalRole.Type == FunctionRole.FunctionRoleType.Guest);
            PageManagement.InitHelpMainMenu(LBTNdefault);
                }

        protected void TreeView1_SelectedNodeChanged(object sender, EventArgs e)
        {
            UI.Common.UserManagement.ProcessTreeViewOnRedirect(this.TreeView1);
        }

        protected void LBTNlogout_Click(object sender, EventArgs e)
        {
            UI.Common.AccessManagement.SignOut(this);
        }

        protected void BTNimportItems_Click(object sender, EventArgs e)
        {
            // import selected items
            switch (Request["Type"])
            {
                case "Associated Equipment":
                    this.importSelectedDevices();
                    break;
                case "Source":
                    this.importSelectedSources();
                    break;
                case "Manufacturer":
                    this.importSelectedManufacturers();
                    break;
            }
        }

        protected void CBXitemChecked_CheckedChanged(object sender, EventArgs e)
        {
            GridViewRow currentRow = (GridViewRow)((Control)sender).Parent.Parent;
            if (((CheckBox)sender).Checked)
            {
                BTNimport1.Enabled = true;

                // Increase selectet item counter 
                Session["SelectedItemCount"] = Convert.ToInt32(Session["SelectedItemCount"]) + 1;

                // Sets the "Selected" column in the DATATABLE! to boolean TRUE
                ((DataTable)Session["SearchResult"]).Rows[currentRow.RowIndex + (GVsearchResult.PageSize * GVsearchResult.PageIndex)][0] = true;

                // Sets its backcolor to yellow, meaning that this item is selected
                currentRow.BackColor = Color.FromArgb(255, 255, 180);
            }
            else
            {
                // Decrease selected item counter
                Session["SelectedItemCount"] = Convert.ToInt32(Session["SelectedItemCount"]) - 1;

                // Checks if item counter equals zero. If so, disable import button
                if (Convert.ToInt32(Session["SelectedItemCount"]) == 0)
                    BTNimport1.Enabled = false;

                // Set row unchecked
                ((DataTable)Session["SearchResult"]).Rows[currentRow.RowIndex + (GVsearchResult.PageSize * GVsearchResult.PageIndex)][0] = false;

                // Set row color
                if (currentRow.RowIndex % 2 == 0)
                    currentRow.BackColor = Color.FromArgb(239, 243, 251);
                else
                    currentRow.BackColor = Color.White;
            }

            BTNimport1.Text = "Import selected items (" + Session["SelectedItemCount"] + ")";
        }

        protected void GVsearchResult_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            // Reset datasource
            GVsearchResult.DataSource = (DataTable)Session["SearchResult"];

            // Set new page index
            GVsearchResult.PageIndex = e.NewPageIndex;

            // Bind data to the GridView
            GVsearchResult.DataBind();

            // Set color of rows (yellow meaning checked)
            foreach (GridViewRow row in GVsearchResult.Rows)
            {
                if (((CheckBox)row.Cells[0].FindControl("CBXitemChecked")).Checked)
                {
                    row.BackColor = Color.FromArgb(255, 255, 180);
                }
                else if (row.RowIndex % 2 == 0)
                    row.BackColor = Color.FromArgb(239, 243, 251);
                else
                    row.BackColor = Color.White;
            }
        }

        protected void GVsearchResult_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            /* Hides the "isSelected" data column of the "SearchResult"-DataTable which contains 
             * a boolean specifying whether the associated checkbox is checked or not. 
             * The checkbox's checked property is bound to this datacolumn!
             */
            if (e.Row.Cells.Count > 1)
                e.Row.Cells[1].Visible = false;
        }

        #endregion
        #region Private helper methods

        private void importSelectedManufacturers()
        {
            try
            {
                                string connectionString = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=RAIS;Integrated Security=True;";
                                //string connectionString = string.Format(@"Data Source=localhost\SQLEXPRESS;Initial Catalog=RAIS;Persist Security Info=True;User ID={0};Password={1}",
                                //                                        Session[RAIS.UI.Common.Constants.IcSRS_LOGIN_TAG], Session[RAIS.UI.Common.Constants.IcSRS_PASSWORD_TAG]);
                // Connect to the database
                SqlConnection con = new SqlConnection(connectionString);
                con.Open();

                // Check each row for import
                foreach (DataRow row in ((DataTable)Session["SearchResult"]).Rows)
                {
                    // Check if row is selected, if so -> import Manufacturer
                    if ((bool)row[0] == true)
                    {
                        #region SET VALUES

                        /* Set the attributes values to the values of the current row
                         * Defaul value is NULL!
                         */

                        string company = "'" + row["Company"].ToString() + "'";

                        string address = "NULL";
                        if (row.Table.Columns.Contains("Address1") && !row["Address1"].ToString().Equals(""))
                            address = "'" + row["Address1"].ToString() + "'";

                        string telephone = "NULL";
                        if (row.Table.Columns.Contains("Telephone1") && !row["Telephone1"].ToString().Equals(""))
                            telephone = "'" + row["Telephone1"].ToString() + "'";

                        string fax = "NULL";
                        if (row.Table.Columns.Contains("Fax") && !row["Fax"].ToString().Equals(""))
                            fax = "'" + row["Fax"].ToString() + "'";

                        string email = "NULL";
                        if (row.Table.Columns.Contains("E-Mail") && !row["E-Mail"].ToString().Equals(""))
                            email = "'" + row["E-Mail"].ToString() + "'";

                        #endregion

                        #region GET COUNTRY FK

                        /* Get the current country foreign key which is needed for adding a new manufacturer
                         * Saves the country FK to a string ("countryFK")
                         */

                        string country = "'" + row["Country"].ToString() + "'";
                        if (country.Equals("'United States of America'"))
                            country = "'USA'";

                        SqlCommand cmd = new SqlCommand("SELECT \"PK Country ID\" FROM Country WHERE \"Country Name\" = " + country, con);
                        SqlDataReader reader = cmd.ExecuteReader();
                        string countryFK = "NULL";
                        reader.Read();
                        if (reader.HasRows)
                            countryFK = reader["PK Country ID"].ToString();
                        reader.Close();
                        #endregion

                        #region CHECK MANUFACTURER NOT IN LIST

                        /* Checks if the selected manufacturer is already in the list.
                         * A manufacturer is already in the list if his name and his address
                         * are equal to another datarow in the DB!
                         */

                        bool alreadyExists = false;
                        cmd = new SqlCommand("SELECT * FROM Manufacturer WHERE Name = " + company, con);
                        reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            if (reader["Address"].Equals(address)) // Manufacturer already exists!
                                alreadyExists = true;
                        }
                        reader.Close();

                        #endregion

                        #region IMPORT MANUFACTURER

                        if (!alreadyExists) // create new manufacturer
                        {
                            reader.Close();
                            if (countryFK.Equals("NULL"))
                                cmd = new SqlCommand("INSERT INTO Manufacturer (Name, Address, Phone, Fax, eMail) VALUES (" + company + ", " + address + ", " + telephone + ", " + fax + ", " + email + ")", con);
                            else
                                cmd = new SqlCommand("INSERT INTO Manufacturer (Name, Address, Phone, Fax, eMail, \"FK Country ID\") VALUES (" + company + ", " + address + ", " + telephone + ", " + fax + ", " + email + ", " + countryFK + ")", con);
                            cmd.ExecuteNonQuery();
                        }
                        else // Update existing manufacturer
                        {
                            reader.Close();
                            if (countryFK.Equals("NULL"))
                                cmd = new SqlCommand("UPDATE Manufacturer SET Address = " + address + ", Phone = " + telephone + ", Fax = " + fax + ", eMail = " + email + " WHERE Name = " + company + " AND Address = " + address, con);
                            else
                                cmd = new SqlCommand("UPDATE Manufacturer SET Address = " + address + ", Phone = " + telephone + ", Fax = " + fax + ", eMail = " + email + ", \"FK Country ID\" = " + countryFK + " WHERE Name = " + company + " AND Address = " + address, con);
                            cmd.ExecuteNonQuery();
                        }

                        #endregion
                    }
                }

                // Close connection and display success message
                con.Close();
                LBLsearch.ForeColor = Color.Green;
                LBLsearch.Text = "Successfully imported " + Session["SelectedItemCount"] + " item(s)!";
                BTNimport1.Visible = false;
                GVsearchResult.Visible = false;
            }
            catch
            {
                LBLsearch.ForeColor = Color.Red;
                LBLsearch.Text = "Data import failed!";
            }
        }

        private void importSelectedDevices()
        {
            try
            {
                // Connect to the database
                                string connectionString = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=RAIS;Integrated Security=True;";
                                //string connectionString = string.Format(@"Data Source=localhost\SQLEXPRESS;Initial Catalog=RAIS;Persist Security Info=True;User ID={0};Password={1}",
                                //                                        Session[RAIS.UI.Common.Constants.IcSRS_LOGIN_TAG], Session[RAIS.UI.Common.Constants.IcSRS_PASSWORD_TAG]);
                                SqlConnection con = new SqlConnection(connectionString);
                con.Open();

                // Check each row for import
                foreach (DataRow row in ((DataTable)Session["SearchResult"]).Rows)
                {
                    // Check if row is selected, if so -> import device
                    if ((bool)row[0] == true)
                    {
                        // Check if device is not already in the list
                        SqlCommand cmd = new SqlCommand("SELECT * FROM \"Asso Model\" WHERE \"Asso Model Name\" = '" + row["DeviceModel"] + "'", con);
                        SqlDataReader reader = cmd.ExecuteReader();
                        
                        if (!reader.HasRows) // add new device
                        {
                            #region GET DEVICE TYPE FK

                            /* Set the device type foreign key which is needed for import
                             * If it's not existing yet -> create new one
                             */

                            cmd = new SqlCommand("SELECT \"PK Asso Type ID\" FROM \"Asso Type\" WHERE \"Type Name\" = '" + row["DeviceType"] + "'", con);
                            reader.Close();
                            reader = cmd.ExecuteReader();
                            string deviceFK = null;
                            reader.Read();
                            if (reader.HasRows) // Device type exists!
                                deviceFK = reader["PK Asso Type ID"].ToString();
                            else // Add new device type
                            {
                                reader.Close();
                                cmd = new SqlCommand("INSERT INTO \"Asso Type\" (\"Type Name\") VALUES ('" + row["DeviceType"] + "')", con);
                                cmd.ExecuteNonQuery();

                                // get new device type FK
                                cmd = new SqlCommand("SELECT \"PK Asso Type ID\" FROM \"Asso Type\" WHERE \"Type Name\" = '" + row["DeviceType"] + "'", con);
                                reader = cmd.ExecuteReader();
                                reader.Read();
                                if (reader.HasRows)
                                    deviceFK = reader["PK Asso Type ID"].ToString();
                            }

                            #endregion

                            #region GET MANUFACTURER FK

                            /* Sets the device type foreign key.
                             * If it's non existent -> create new manufacturer
                             */

                            cmd = new SqlCommand("SELECT \"PK Manufacturer ID\" FROM Manufacturer WHERE Name = '" + row["Manufacturers"] + "'", con);
                            reader.Close();
                            reader = cmd.ExecuteReader();
                            string manufacturerFK = "NULL";
                            reader.Read();
                            if (reader.HasRows) // manufacturer exists!
                            {
                                manufacturerFK = reader["PK Manufacturer ID"].ToString();
                                reader.Close();
                            }
                            else // add new manufacturer
                            {
                                reader.Close();
                                cmd = new SqlCommand("INSERT INTO Manufacturer (Name) VALUES ('" + row["Manufacturers"] + "')", con);
                                cmd.ExecuteNonQuery();
                                
                                // get new manufacturer FK
                                cmd = new SqlCommand("SELECT \"PK Manufacturer ID\" FROM Manufacturer WHERE Name = '" + row["Manufacturers"] + "'", con);
                                reader = cmd.ExecuteReader();
                                reader.Read();
                                if (reader.HasRows)
                                    manufacturerFK = reader["PK Manufacturer ID"].ToString();
                                reader.Close();
                            }

                            #endregion

                            #region INSERT NEW DEVICE

                            cmd = new SqlCommand("INSERT INTO \"Asso Model\" (\"Asso Model Name\", \"FK Asso Type ID\", \"FK Manufacturer ID\") VALUES ('" + row["DeviceModel"] + "', '" + deviceFK + "', '" + manufacturerFK + "')", con);
                            cmd.ExecuteNonQuery();

                            #endregion
                        }
                        reader.Close();
                    }
                }

                // Close connection and display success message
                con.Close();
                LBLsearch.ForeColor = Color.Green;
                LBLsearch.Text = "Successfully imported " + Session["SelectedItemCount"] + " item(s)!";
                BTNimport1.Visible = false;
                GVsearchResult.Visible = false;
            }
            catch
            {
                LBLsearch.ForeColor = Color.Red;
                LBLsearch.Text = "Data import failed!";
            }
        }

        private void importSelectedSources()
        {
            try
            {
                // Connect to the database
                                string connectionString = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=RAIS;Integrated Security=True;";
                                //string connectionString = string.Format(@"Data Source=localhost\SQLEXPRESS;Initial Catalog=RAIS;Persist Security Info=True;User ID={0};Password={1}",
                                //                                        Session[RAIS.UI.Common.Constants.IcSRS_LOGIN_TAG], Session[RAIS.UI.Common.Constants.IcSRS_PASSWORD_TAG]);
                                SqlConnection con = new SqlConnection(connectionString);
                con.Open();

                // Check each row for import
                foreach (DataRow row in ((DataTable)Session["SearchResult"]).Rows)
                {
                    // Check if row is selected, if so -> import source
                    if ((bool)row[0] == true)
                    {
                        // Check if source is not already in the list
                        SqlCommand cmd = new SqlCommand("SELECT * FROM \"Sealed Model\" WHERE \"Sealed Model Name\" = '" + row["SourceModel"] + "'", con);
                        SqlDataReader reader = cmd.ExecuteReader();

                        // Check if source is not existing yet. If so -> add new source
                        if (!reader.HasRows)
                        {
                            #region GET MANUFACTURER FK

                            /* Sets the device type foreign key.
                             * If it's non existent -> create new manufacturer
                             */

                            cmd = new SqlCommand("SELECT \"PK Manufacturer ID\" FROM Manufacturer WHERE Name = '" + row["Manufacturers"] + "'", con);
                            reader.Close();
                            reader = cmd.ExecuteReader();
                            string manufacturerFK = "NULL";
                            reader.Read();
                            if (reader.HasRows) // manufacturer exists!
                            {
                                manufacturerFK = reader["PK Manufacturer ID"].ToString();
                                reader.Close();
                            }
                            else // add new manufacturer
                            {
                                reader.Close();
                                cmd = new SqlCommand("INSERT INTO Manufacturer (Name) VALUES ('" + row["Manufacturers"] + "')", con);
                                cmd.ExecuteNonQuery();

                                // get new manufacturer FK
                                cmd = new SqlCommand("SELECT \"PK Manufacturer ID\" FROM Manufacturer WHERE Name = '" + row["Manufacturers"] + "'", con);
                                reader = cmd.ExecuteReader();
                                reader.Read();
                                if (reader.HasRows)
                                    manufacturerFK = reader["PK Manufacturer ID"].ToString();
                                reader.Close();
                            }

                            #endregion

                            #region INSERT NEW SOURCE

                            cmd = new SqlCommand("INSERT INTO \"Sealed Model\" (\"Sealed Model Name\", \"FK Manufacturer ID\") VALUES ('" + row["SourceModel"] + "', '" + manufacturerFK + "')", con);
                            cmd.ExecuteNonQuery();

                            #endregion
                        }

                        reader.Close();
                    }
                }

                // Close connection and display success message
                con.Close();
                LBLsearch.ForeColor = Color.Green;
                LBLsearch.Text = "Successfully imported " + Session["SelectedItemCount"] + " item(s)!";
                BTNimport1.Visible = false;
                GVsearchResult.Visible = false;
            }
            catch
            {
                LBLsearch.ForeColor = Color.Red;
                LBLsearch.Text = "Data import failed!";
            }
        }

        #endregion
    }
}